from flask import Blueprint, request, render_template, jsonify
from utils.path_utils import get_project_root
import duckdb, uuid, os
from database.duckdb.duckdb_base import get_connection


# 创建 Blueprint
database = Blueprint('database', __name__)


def start(conn):
    conn.execute("""
    CREATE TABLE IF NOT EXISTS sql_history (
        id UUID PRIMARY KEY,
        query TEXT,
        create_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );
    """)
    conn.execute("""
    CREATE TABLE IF NOT EXISTS sql_save (
        id UUID PRIMARY KEY,
        query TEXT,
        name VARCHAR,
        create_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );
    """)

@database.route('/objects/type', methods=['GET'])
def get_objects_type():
    conn = get_connection()
    start(conn)
    result = conn.execute("SELECT table_type FROM information_schema.tables GROUP BY table_type").fetchdf()
    result = result.to_dict('records')
    conn.close()
    return jsonify({"rows": result})

@database.route('/objects', methods=['GET'])
def get_objects():
    obj_type = request.args.get('type', 'table')
    query = f"SELECT table_name FROM information_schema.tables WHERE table_type='{obj_type.upper()}'"
    conn = get_connection()
    result = conn.execute(query).fetchall()
    conn.close()
    response = jsonify([row[0] for row in result])
    response.headers["Content-Type"] = "application/json"
    return response

@database.route('/object_info', methods=['GET'])
def get_object_info():
    name = request.args.get('name')
    if not name:
        return jsonify({"error": "Missing name"}), 400
    conn = get_connection()
    columns = conn.execute(f"DESCRIBE {name}").fetchall()
    data = conn.execute(f"SELECT * FROM {name} LIMIT 100").fetchdf()
    data = data.to_dict('records')
    view_code = ""
    rows = conn.execute("SELECT table_name FROM information_schema.tables where table_type = 'VIEW'").fetchall()
    for row in rows:
        if row[0] == name:
            view_code = conn.execute(f"SELECT sql FROM duckdb_views WHERE view_name = '{name}'").fetchone()[0]
            view_code = view_code.replace("CREATE", "CREATE OR REPLACE")
            break

    conn.close()
    return jsonify({"columns": columns, "data": data, "view_code": view_code})


@database.route("/query", methods=["POST"])
def query_db():
    try:
        data = request.json
        sql = data.get("sql")
        page = int(data.get("page", 1))
        page_size = int(data.get("page_size", 10))

        if not sql:
            return jsonify({"error": "SQL query is required"}), 200

        conn = get_connection()
        cursor = conn.execute(sql)

        # 获取列名
        column_names = [desc[0] for desc in cursor.description]
        result = cursor.fetchall()
        conn.close()

        total_rows = len(result)
        start_idx = (page - 1) * page_size
        end_idx = start_idx + page_size
        paginated_result = result[start_idx:end_idx]

        # 组装为字典格式
        formatted_result = [
            dict(zip(column_names, row)) for row in paginated_result
        ]

        return jsonify({
            "data": formatted_result,
            "columns": column_names,
            "total": total_rows,
            "page": page,
            "page_size": page_size
        })

    except Exception as e:
        return jsonify({"error": str(e)}), 200

@database.route("/history/save", methods=["POST"])
def save_history():
    data = request.json
    sql = data.get("sql")
    uuid_now = uuid.uuid4()

    try:
        conn = get_connection()
        conn.execute("delete from sql_history where query = ?", [sql])
        conn.execute("INSERT INTO sql_history (id, query) VALUES (?, ?)", (uuid_now, sql))
        conn.close()
        return jsonify({"message": "SQL 已保存"})
    except Exception as e:
        return jsonify({"error": str(e)})

@database.route("/history/get", methods=["GET"])
def get_history():
    try:
        conn = get_connection()
        rows = conn.execute("SELECT query, create_at FROM sql_history ORDER BY create_at desc LIMIT 50").fetchdf()
        rows = rows.to_dict('records')
        conn.close()
        return jsonify({"history": rows})
    except Exception as e:
        return jsonify({"error": str(e)})
    
@database.route("/sql/save", methods=["POST"])
def save_sql():
    data = request.json
    sql = data.get("sql")
    name = data.get("name")
    uuid_now = uuid.uuid4()

    try:
        conn = get_connection()
        conn.execute("INSERT INTO sql_save (id, query, name) VALUES (?, ?, ?)", (uuid_now, sql, name))
        conn.close()
        return jsonify({"message": "SQL 已保存"})
    except Exception as e:
        return jsonify({"error": str(e)})

@database.route("/sql/get", methods=["GET"])
def get_sql():
    try:
        conn = get_connection()
        rows = conn.execute("SELECT id, name, query, create_at FROM sql_save ORDER BY create_at desc").fetchdf()
        rows = rows.to_dict('records')
        conn.close()
        return jsonify({"history": rows})
    except Exception as e:
        return jsonify({"error": str(e)})
    
@database.route("/sql/delete", methods=["Delete"])
def delete_sql():
    id = request.args.get('id')
    try:
        conn = get_connection()
        conn.execute("delete from sql_save where id = ?", [id])
        conn.close()
        return jsonify({"message": "success"})
    except Exception as e:
        return jsonify({"error": str(e)})



@database.route('/showtables')
def showtables():
    conn = duckdb.connect('data.db')
    tables = conn.execute("SHOW TABLES").fetchall()
    conn.close()
    return render_template('dbsql.html', tables=tables)

@database.route('/table/<name>')
def table(name):
    conn = duckdb.connect('data.db')
    create_view_sql = conn.execute(f"select sql from duckdb_views() where view_name = '{name}'").fetchone() if name.startswith('v_') else None
    columns = conn.execute(f"DESCRIBE {name}").fetchall()
    data = conn.execute(f"SELECT * FROM {name}").df()
    conn.close()
    return render_template('table.html', table_name=name, create_view_sql=create_view_sql, columns=columns, data=data)
